package org.comet.auxiliary.client.liaobei.sql;

import org.comet.auxiliary.client.swing.versions.v1.SettingFrame;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class SqliteHelper {
    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;
    private String dbFilePath;

    /**
     * 构造函数
     *
     * @param dbFilePath sqlite db 文件路径
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public SqliteHelper(String dbFilePath) throws ClassNotFoundException, SQLException {
        this.dbFilePath = dbFilePath;
        connection = getConnection(dbFilePath);
        this.init();
    }


    /**
     * 获取数据库连接
     *
     * @param dbFilePath db文件路径
     * @return 数据库连接
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public Connection getConnection(String dbFilePath) throws ClassNotFoundException, SQLException {
        Connection conn = null;
        Class.forName("org.sqlite.JDBC");
        conn = DriverManager.getConnection("jdbc:sqlite:" + dbFilePath);
        return conn;
    }

    /**
     * 执行sql查询
     *
     * @param sql sql select 语句
     * @param rse 结果集处理类对象
     * @return 查询结果
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public <T> List<Map<String, Object>> executeQuery(String sql, ResultSetExtractor<T> rse) throws SQLException, ClassNotFoundException {
        try {
            resultSet = getStatement().executeQuery(sql);
            List<Map<String, Object>> rs = rse.extractData(resultSet);
            return rs;
        } finally {
            destroyed();
        }
    }

    /**
     * 执行select查询，返回结果列表
     *
     * @param sql sql select 语句
     * @param rm  结果集的行数据处理类对象
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public <T> List<T> executeQuery(String sql, RowMapper<T> rm) throws SQLException, ClassNotFoundException {
        Connection conn = this.connection;

        Statement stat = conn.createStatement();
        List<T> rsList = new ArrayList<T>();
        try {

            resultSet = stat.executeQuery(sql);
            while (resultSet.next()) {
                rsList.add(rm.mapRow(resultSet, resultSet.getRow()));
            }
        } finally {
            destroyed();
        }
        return rsList;
    }

    /**
     * 执行数据库更新sql语句
     *
     * @param sql
     * @return 更新行数
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public int executeUpdate(String sql) throws SQLException, ClassNotFoundException {
        try {
            int c = getStatement().executeUpdate(sql);
            return c;
        } finally {
            destroyed();
        }

    }

    /**
     * 执行多个sql更新语句
     *
     * @param sqls
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void executeUpdate(String... sqls) throws SQLException, ClassNotFoundException {
        try {
            for (String sql : sqls) {
                getStatement().executeUpdate(sql);
            }
        } finally {
            destroyed();
        }
    }

    /**
     * 执行数据库更新 sql List
     *
     * @param sqls sql列表
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void executeUpdate(List<String> sqls) throws SQLException, ClassNotFoundException {
        try {
            for (String sql : sqls) {
                getStatement().executeUpdate(sql);
            }
        } finally {
            destroyed();
        }
    }

    private Connection getConnection() throws ClassNotFoundException, SQLException {
        if (null == connection) connection = getConnection(dbFilePath);
        return connection;
    }

    private Statement getStatement() throws SQLException, ClassNotFoundException {
        if (null == statement) statement = getConnection().createStatement();
        return statement;
    }

    /**
     * 数据库资源关闭和释放
     */
    public void destroyed() {
        try {
            if (null != connection) {
                connection.close();
                connection = null;
            }

            if (null != statement) {
                statement.close();
                statement = null;
            }

            if (null != resultSet) {
                resultSet.close();
                resultSet = null;
            }
        } catch (SQLException e) {
            System.out.println("数据库关闭异常" + e.getMessage());
        }
    }


    //初始化数据库
    public void init() throws SQLException {

        //建立一个数据库名zieckey.db的连接，如果不存在就在当前目录下创建之

        Connection conn = this.connection;

        Statement stat = conn.createStatement();
        //创建配置表
//        stat.executeUpdate("DROP TABLE IF EXISTS `config`;");
        stat.executeUpdate("create table if not exists config(userid varchar ,money int ,page_num int,title varchar,jg int,sendId varchar ,znhm int,lbjx int,f int,f_text varchar ,zltzfb int,cctzfb,k int,k_text varhcar,lqslhb int,lqshlb_text varchar ,bl double);");
        stat.executeUpdate("create table if not exists userdata(fbls double,send_page_num int,lq_money double,yk double,userid varchar );");
        stat.executeUpdate("create table if not exists userinfo(name varchar ,uid varchar ,uuid varchar ,tk varchar );");
        stat.executeUpdate("create table if not exists title(name varchar,userid varchar )");
        stat.executeUpdate("create table if not exists reddata(phone varchar,name varchar,groupname varchar ,groupno varchar ,sendnum int,sendmoney double,yk double,userid varchar,lqmoney double )");

//        ResultSet rs = stat.executeQuery("select * from userdata where userid="+SettingFrame.userInfo.getUid()); //查询数据
//        if(!rs.next()){
//            stat.executeUpdate("insert into userdata values(null,null,null,"+ SettingFrame.userInfo.getUid()+")");
//        }





//        stat.executeUpdate("");


//        //        ResultSet rs = stat.executeQuery("select * from tbl1;"); //查询数据
//
//        while (rs.next()) { //将查询到的数据打印出来
//
//            System.out.print("name = " + rs.getString("name") + " "); //列属性一
//
//            System.out.println("salary = " + rs.getString("salary")); //列属性二
//
//        }

    }


}
